CREATE external TABLE jms_dwd.dwd_wide_tms_road_waybill_dt(
shipment_id bigint comment'任务ID',
shipment_no string comment'任务编号',
shipment_name string comment'任务名称',
billcode string comment'运单号',
packagecode string comment'包号',
package_inside_charge_weight decimal(10,4) comment'内部计费重量:单位千克',
package_total_volume decimal(10,4) comment'包裹总体积:单位立方厘米',
package_total_weight decimal(10,4) comment'包裹总重量:单位千克',
waybill_weight decimal(10,4) comment'运单重量',
terminal_dispatch_code string comment'三段码',
start_code string comment'始发中心编号',
start_name string comment'始发中心名称',
end_code string comment'目的中心编号',
end_name string comment'目的中心名称',
shipment_site_cnt int comment'途径地数量',
vehicleline_id string comment'参考车线ID',
vehicleline_code string comment'参考车线编码',
vehicleline_name string comment'参考车线名称',
shifts int comment'班次',
business_attribute int comment'业务属性',
operation_model string comment'运行模式',
shipment_type int comment'任务类型',
shipno_planned_departure_time timestamp comment'规划发车时间',
shipno_planned_arrival_time timestamp comment'规划到车时间',
shipno_actual_departure_time timestamp comment'实际发车时间',
shipno_actual_arrival_time timestamp comment'实际到车时间',
total_runtime timestamp comment'总运行时间(MIN)',
total_intransittime timestamp comment'总在途时间(MIN)',
span_days int comment'跨越天数',
standard_speed decimal(10,4) comment'标准时速(KM/H)',
mileage decimal(10,4) comment'系统里程(KM)',
roadsection_id int comment'路段编号',
--stop_time timestamp comment'经停时长',
usetime timestamp comment'运输耗时(MIN)',
road_mileage decimal(10,4) comment'规划里程(KM)',
speed decimal(10,4) comment'时速(KM/H)',
batch_time int comment'批次时间',
plan_driving_time timestamp comment'规定行驶时间',
actual_driving_time timestamp comment'实际行驶时间',
estimate_batch_time int comment'应赶批次',
actual_batch_time timestamp comment'实赶批次时间',
start_site_sort_num int comment'始发站点顺序编号',
start_site_code string comment'始发站点编号',
start_site_name string comment'始发站点名称',
load_start_time timestamp comment'开始装货',
load_end_time timestamp comment'装货完成',
lock_time timestamp comment'锁车时间',
load_scan_time timestamp comment'装车扫描时间',
load_next_site_code string comment'装车下一站编码',
load_next_site_name string comment'装车下一站名称',
start_handling_type string comment'始发站点装卸类型',
send_car_scan_time timestamp comment'封发车扫描时间',
send_car_code string comment'封发车号',
app_departure_time timestamp comment'APP离开时间',
gps_departure_time timestamp comment'GPS离开时间',
weight_departure_time timestamp comment'过磅出站时间',
planned_departure_time timestamp comment'计划离开时间',
actual_departure_time timestamp comment'实际离开时间',
departure_gross_weight decimal(10,4) comment'离开毛重',
departure_tare_weight decimal(10,4) comment'离开皮重',
departure_net_weight decimal(10,4) comment'离开净重',
gps_depart_mileage decimal(10,4) comment'GPS发车里程',
site_send_time timestamp comment'站点发件扫描',
send_next_site_code string comment'站点发件下一站编码',
send_next_site_name string comment'站点发件下一站名称',
end_site_sort_num int comment'目的站点顺序编号',
end_site_code string comment'目的站点编号',
end_site_name string comment'目的站点名称',
unload_start_time timestamp comment'开始卸货',
unload_end_time timestamp comment'卸货完成',
unlock_time timestamp comment'解锁时间',
unload_scan_time timestamp comment'卸车扫描时间',
unload_pre_site_code string comment'卸车扫描上一站编码',
unload_pre_site_name string comment'卸车扫描上一站名称',
end_handling_type string comment'目的站点装卸类型',
arrival_car_scan_time timestamp comment'到解车扫描时间',
arrival_car_code string comment'到解车号',
app_arrival_time timestamp comment'APP到达时间',
gps_arrival_time timestamp comment'GPS到达时间',
weight_arrival_time timestamp comment'过磅进站时间',
planned_arrival_time timestamp comment'计划到达时间',
actual_arrival_time timestamp comment'实际到达时间',
arrival_gross_weight decimal(10,4) comment'到达毛重',
arrival_tare_weight decimal(10,4) comment'到达皮重',
arrival_net_weight decimal(10,4) comment'到达净重',
gps_arrival_mileage decimal(10,4) comment'GPS到车里程',
site_arrival_time timestamp comment'站点到件扫描',
arrival_pre_site_code string comment'站点到件下一站编码',
arrival_pre_site_name string comment'站点到件下一站名称',
latest_arrival_time timestamp comment'最晚到车时间',
plate_number string comment'车牌号',
vehicletype_id string comment'车型ID',
vehicletype_name string comment'车型名称',
trailer_number string comment'挂车号',
carrier_id string comment'承运商ID',
carrier_type string comment'承运商类型',
carrier_name string comment'承运商全名',
driver_name string comment'司机名称',
driver_contact string comment'随车电话',
load_weight decimal(10,4) comment'核定质量(KG)',
vehicle_volume decimal(10,4) comment'核定体积（M³）',
nuclear_load_num decimal(10,4) comment'载票数',
quotation_model string comment'报价模式',
billing_way int comment'计费方式（1：整车计重、2：零担计重、3：按趟计费）',
settlement_vehicle_type_id string comment'结算车型ID',
settlement_vehicle_type_name string comment'结算车型名称',
freight_generate_status int comment'运费流水生成状态：1',
overtime_type tinyint comment'加班类型：11爆仓加班、12顶班、13应急加班',
shipment_state int comment'任务状态:待调度0',
is_ballast int comment'是否空放:1是',
is_trunk_branch int comment'支干线：1-干线；2-支线',
is_stop int comment'是否经停',
is_assist_line int comment'是否辅线',
vehicleline_type int comment'线路类型',
has_out int comment'是否兜底'
)
partitioned by (dt string comment'时间分区-任务号规划发车时间')
stored as parquet
location '/dw/hive/jms_dwd.db/external/dwd_wide_tms_road_waybill_dt'
tblproperties (
'discover.partitions' = 'false',
'parquet.column.index.access' = 'true'
);